﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using MySql.Data.MySqlClient;
using FetchDataSystem.Model;

namespace FetchDataSystem.DAL
{
    public class MountGuardService
    {   
        /// <summary>
        /// 根据员工号查询员工对象
        /// </summary>
        /// <param name="employeeNo"></param>
        /// <returns></returns>
        public static Employee GetEmployeeByEmployeeNo(string employeeNo)
        {
            Employee employee = null;
            string sql = "SELECT * from bas_employees WHERE employeeNo=@employeeNo";
            using(MySqlDataReader dr=DBHelper.ExecuteReader(DBHelper.ConnectionString,CommandType.Text,sql,new MySqlParameter("@employeeNo", employeeNo)))
            {
                if (dr.Read())
                {
                    employee = new Employee();
                    employee.id = dr["id"].ToString();
                    employee.EmployeeNo = dr["employeeNo"].ToString();
                    employee.EmployeeName = dr["employeeName"].ToString();

                }
            }
            return employee;
        }
        /// <summary>
        /// 根据员工id获得员工与工位关系id
        /// </summary>
        /// <param name="employeeId"></param>
        /// <returns></returns>
        public static string GetEmployeeCellByEmployeeId(string employeeId)
        {
            string id = null;
            string sql = "SELECT * FROM bas_cell_employee WHERE employee_id=@employeeId";
            using (MySqlDataReader dr = DBHelper.ExecuteReader(DBHelper.ConnectionString, CommandType.Text, sql, new MySqlParameter("@employeeId", employeeId)))
            {
                if (dr.Read())
                {
                    id = dr["id"].ToString();
                }
            }
            return id;
        }

        /// <summary>
        /// 根据员工号获取上岗信息
        /// </summary>
        /// <param name="employeeNo"></param>
        /// <returns></returns>
        public static MountGuard GetMountGuardByEmplloyeeNo(string employeeNo)
        {
            MountGuard mountGuard = null;
            string sql = "SELECT * FROM mount_guard WHERE employeeNo=@employeeNo AND clockOff is NULL ORDER BY clockIn DESC LIMIT 1";
            using (MySqlDataReader dr = DBHelper.ExecuteReader(DBHelper.ConnectionString, CommandType.Text, sql, new MySqlParameter("@employeeNo", employeeNo)))
            {
                if (dr.Read())
                {
                    mountGuard = new MountGuard();
                    mountGuard.id = dr["id"].ToString();
                    mountGuard.EmployeeId = dr["employee_id"].ToString();
                    mountGuard.EmployeeNo = dr["employeeNo"].ToString();
                    mountGuard.EmployeeName = dr["employeeName"].ToString();
                    mountGuard.LineId = dr["line_id"].ToString();
                    mountGuard.LineName = dr["lineName"].ToString();
                    mountGuard.StationId = dr["station_id"].ToString();
                    mountGuard.StationName = dr["stationName"].ToString();
                    mountGuard.CellId = dr["cell_id"].ToString();
                    mountGuard.CellName = dr["cellName"].ToString();
                    if (!(dr["clockIn"] is DBNull))
                    {
                        mountGuard.ClockIn = Convert.ToDateTime(dr["clockIn"]);
                    }
                    if (!(dr["clockOff"] is DBNull))
                    {
                        mountGuard.ClockOff = Convert.ToDateTime(dr["clockOff"]);
                    }
                    mountGuard.WorkStatus = dr["workStatus"].ToString();
                    mountGuard.DelFlag = dr["del_flag"].ToString();
                    mountGuard.Remarks = dr["remarks"].ToString();
                    mountGuard.CreateBy = dr["create_by"].ToString();
                    if (!(dr["create_date"] is DBNull))
                    {
                        mountGuard.CreateDate = Convert.ToDateTime(dr["create_date"]);
                    }
                    mountGuard.UpdateBy = dr["update_by"].ToString();
                    if (!(dr["update_date"] is DBNull))
                    {
                        mountGuard.UpdateDate = Convert.ToDateTime(dr["update_date"]);
                    }

                }
            }
            return mountGuard;
        }
        /// <summary>
        /// 保存上岗记录
        /// </summary>
        /// <param name="mountGuard"></param>
        /// <returns></returns>
        public static int InsertMountGuard(MountGuard mountGuard)
        {
            int result = 0;//保存返回结果
            try
            {
                //1.丰富MountGuard对象的其他属性值
                string sql = @"SELECT a.id as employeeId,a.employeeNo,a.employeeName,a.officeId,b.cell_id,c.cellName,
                        c.station_id,d.stationName,d.line_id,e.lineName
                        FROM bas_employees a INNER JOIN bas_cell_employee b on b.employee_id=a.id
                        LEFT JOIN bas_workcell c on c.id = b.cell_id
                        LEFT JOIN bas_workstationinfos d on d.id = c.station_id
                        LEFT JOIN bas_line e on e.id = d.line_id
                        WHERE a.id = @employeeId";
                using (MySqlDataReader dr = DBHelper.ExecuteReader(DBHelper.ConnectionString, CommandType.Text, sql, new MySqlParameter("@employeeId", mountGuard.EmployeeId)))
                {
                    if (dr.Read())
                    {
                        mountGuard.OfficeId = dr["officeId"].ToString();
                        mountGuard.EmployeeName = dr["employeeName"].ToString();
                        mountGuard.CellId = dr["cell_id"].ToString();
                        mountGuard.CellName = dr["cellName"].ToString();
                        mountGuard.StationId = dr["station_id"].ToString();
                        mountGuard.StationName = dr["stationName"].ToString();
                        mountGuard.LineId = dr["line_id"].ToString();
                        mountGuard.LineName = dr["lineName"].ToString();
                    }
                }
                //2.保存上岗记录
                sql = @"INSERT INTO mount_guard VALUES(@id,@employee_id,@employeeNo,
                    @employeeName,@office_id,@line_id,@lineName,@station_id,
                    @stationName,@cell_id,@cellName,@clockIn,@clockOff,@workStatus,
                    @del_flag,@remarks,@create_by,@create_date,@update_by,@update_date);";
                MySqlParameter[] pars = new MySqlParameter[]
                {
                new MySqlParameter("@id",mountGuard.id),
                new MySqlParameter("@employee_id",mountGuard.EmployeeId),
                new MySqlParameter("@employeeNo",mountGuard.EmployeeNo),
                new MySqlParameter("@employeeName",mountGuard.EmployeeName),
                new MySqlParameter("@office_id",mountGuard.OfficeId),
                new MySqlParameter("@line_id",mountGuard.LineId),
                new MySqlParameter("@lineName",mountGuard.LineName),
                new MySqlParameter("@station_id",mountGuard.StationId),
                new MySqlParameter("@stationName",mountGuard.StationName),
                new MySqlParameter("@cell_id",mountGuard.CellId),
                new MySqlParameter("@cellName",mountGuard.CellName),
                new MySqlParameter("@clockIn",mountGuard.ClockIn),
                new MySqlParameter("@clockOff",DBNull.Value),//数据库表中的NUll
                new MySqlParameter("@workStatus",mountGuard.WorkStatus),
                new MySqlParameter("@del_flag",mountGuard.DelFlag),
                new MySqlParameter("@remarks",mountGuard.Remarks),
                new MySqlParameter("@create_by",mountGuard.CreateBy),
                new MySqlParameter("@create_date",mountGuard.CreateDate),
                new MySqlParameter("@update_by",mountGuard.UpdateBy),
                new MySqlParameter("@update_date",mountGuard.UpdateDate)
            };
                result = DBHelper.ExecuteNonQuery(DBHelper.ConnectionString, CommandType.Text, sql, pars);
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
                return 0;
            }

            return result;
        }
        /// <summary>
        /// 更新上岗记录
        /// </summary>
        /// <param name="mountGuard"></param>
        /// <returns></returns>
        public static int UpdateMountGuard(MountGuard mountGuard)
        {
            int result = 0;
            try
            {
                string sql = "UPDATE mount_guard set clockOff=@clockOff,workStatus=@workStatus,update_by=@update_by,update_date=@update_date WHERE id=@id";

                MySqlParameter[] pars = new MySqlParameter[]
                {
                new MySqlParameter("@clockOff",mountGuard.ClockOff),
                new MySqlParameter("@workStatus",mountGuard.WorkStatus),
                new MySqlParameter("@update_by",mountGuard.UpdateBy),
                new MySqlParameter("@update_date",mountGuard.UpdateDate),
                new MySqlParameter("@id",mountGuard.id)
                };
                result = DBHelper.ExecuteNonQuery(DBHelper.ConnectionString, CommandType.Text, sql, pars);
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
                return 0;
            }
            return result;
        }
    }
}
